//
//  UserDataBaseHandle.m
//  UI_元旦豆瓣
//
//  Created by dllo on 16/1/9.
//  Copyright © 2016年 31付冬梅. All rights reserved.
//

#import "UserDataBaseHandle.h"
#import "UserModel.h"
@implementation UserDataBaseHandle

+ (UserDataBaseHandle *)sharedUserDataBaseHandle {
    static UserDataBaseHandle *userDB = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        userDB = [[UserDataBaseHandle alloc] init];
    });
    return userDB;
}
- (void)openUserDB {
    // 获取到沙盒路径
    NSString *document = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    // 拼接数据库路径
    NSString *dbPath = [document stringByAppendingPathComponent:@"AfraDataBase20160109User.sqlite"];
    NSLog(@"沙盒: %@", dbPath);
    
    //打开数据库
    int result = sqlite3_open(dbPath.UTF8String, &UserDataBase);//返回执行结果
    if (result == SQLITE_OK) {
        NSLog(@"打开成功");
    } else {
        NSLog(@"打开失败");
    }

}

- (void)creatUserTable {
    // sql语句
    NSString *sqlStr = [NSString stringWithFormat:@"create table if not exists User(number integer primary key autoincrement, userName text, password text)"];//主键 自增
    // 让数据库执行sql语句（数据库必须是打开的状态）
    int result = sqlite3_exec(UserDataBase, sqlStr.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"建表成功");
    } else {
        NSLog(@"建表失败");
    }
}

- (void)insertUserModel:(UserModel *)model {
    // sql语句
    NSString *sqlStr = [NSString stringWithFormat:@"insert into User(userName, password) values('%@', '%@')", model.userName, model.password];
    
    int result = sqlite3_exec(UserDataBase, sqlStr.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    } else {
        NSLog(@"插入失败");
    }
}

- (void)deleteUserModel:(UserModel *)model {
    NSString *sqlStr = [NSString stringWithFormat:@"delete from User where userName = '%@'", model.userName];
    int result = sqlite3_exec(UserDataBase, sqlStr.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
    
}

- (NSMutableArray *)selectUserTable {
    NSString *sqlStr = [NSString stringWithFormat:@"select userName from User"];
    // 创建一个跟随指针，用来遍历数据库中的每一条数据
    sqlite3_stmt *stmt = nil;
    
    // 进入查询
    // 第三个参数：字段的长度没有限制
    int result = sqlite3_prepare_v2(UserDataBase, sqlStr.UTF8String, -1, &stmt, NULL);
    
    //创建一个数组 用来接受model
    NSMutableArray *arrModel = [NSMutableArray array];
    if (result == SQLITE_OK) {
        // 遍历
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 从数据库中取出数据
            const unsigned char *userName = sqlite3_column_text(stmt, 1);//neme编号是1
            const unsigned char *password = sqlite3_column_text(stmt, 2);
           
            // 创建model 给model赋值
            UserModel *model = [UserModel userModel];
            model.userName = [NSString stringWithUTF8String:(const char *)userName];
            model.password = [NSString stringWithUTF8String:(const char *)password];
            
            [arrModel addObject:model];
            
        }
    } else {
        NSLog(@"查询失败");
    }
    
    
    
    return arrModel;
}




@end
